package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import bean.designerBean;

public class designerDao {
	
	public void inserir(Connection conn, designerBean designer) throws SQLException {

		String sql = "insert into designer(iddesigner, idos) values(?,?)";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, designer.getIddesigner());
		ps.setInt(2, designer.getIdos());
		ps.execute();
		ps.close();
	}

	public void alterar(Connection conn, designerBean designer) throws SQLException {

		String sql = "update designer set iddesigner = ?, idos = ?, where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, designer.getIddesigner());
		ps.setInt(2, designer.getIdos());
		ps.execute();
		ps.close();
	}

	public void remover(Connection conn, designerBean designer) throws SQLException {

		String sql = "delete from designer where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, designer.getId());
		ps.execute();
		ps.close();
	}

	public ArrayList<designerBean> getLista(Connection conn) throws SQLException {

		String sql = "select * from designer";

		PreparedStatement ps = conn.prepareStatement(sql);

		ResultSet rs = ps.executeQuery();
		ArrayList<designerBean> designers = new ArrayList<designerBean>();
		while (rs.next()) {
			designerBean designer = new designerBean();
			designer.setId(rs.getInt("id"));
			designer.setIddesigner(rs.getInt("iddesigner"));
			designer.setIdos(rs.getInt("idos"));
			
			designers.add(designer);
		}
		ps.close();
		rs.close();

		return designers;

	}
	
	public designerBean getdesigner(Connection conn, designerBean designerBean)
			throws SQLException {

		String sql = "select os.id as idos, designer.id as id, designer.iddesigner as iddesigner, funcionario.nome as nome from os, designer, funcionario where os.id = ? and os.id = designer.idos and designer.iddesigner = funcionario.id";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, designerBean.getIdos());
		
		ResultSet rs = ps.executeQuery();
		
		designerBean designer = new designerBean();
		if(rs.next()){
			designer.setId(rs.getInt("id"));
			designer.setIddesigner(rs.getInt("iddesigner"));
			designer.setIdos(rs.getInt("idos"));
			designer.setNome(rs.getString("nome"));
		}
		ps.close();
		rs.close();
		
		return designer;

	}
	
	public boolean isOsDesigner(Connection conn, designerBean designer) throws SQLException{
		
		String sql = "select * from os, designer where designer.idos = os.id and os.situacao in ('voltouDesigner','designer') and ativo='1' and designer.iddesigner = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, designer.getIddesigner());
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			return true;
		}
		ps.close();
		rs.close();
		return false;
		
	}

}

